from datetime import datetime
from typing import Literal

from pydantic import BaseModel
from sqlalchemy import Column, Integer, String, Boolean, DateTime, select, or_, func
from sqlalchemy.orm import declarative_base

from utils.drivers.database.engine import AsyncSession
from utils.exception import StatusCode, AppException
from utils.enums import ArticleStatus, Sort, Language, TimeKey

Base = declarative_base()

class NewsModel(BaseModel):
    id: int
    title: str
    news_type_id: int
    news_type_name: str
    brief: str
    text: str
    status: ArticleStatus
    cover: bool
    create_time: datetime
    update_time: datetime


class News(Base):
    __tablename__ = "news"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(255))
    title_en = Column(String(255))
    news_type = Column(Integer)
    brief = Column(String(255))
    brief_en = Column(String(255))
    text = Column(String)
    text_en = Column(String)
    status = Column(String(255))
    cover = Column(Boolean)
    create_time = Column(DateTime, default=datetime.now())
    update_time = Column(DateTime, default=datetime.now(), onupdate=datetime.now())


    def model(self, language: Language, news_type_list: list = None) -> NewsModel:
        return NewsModel(
            id=self.id,  # type: ignore
            title=self.title if language == Language.CN else self.title_en,  # type: ignore
            news_type_id=self.news_type,  # type: ignore
            news_type_name=[nt for nt in news_type_list if nt.id == self.news_type][0].name if news_type_list else "",
            brief=self.brief if language == Language.CN else self.brief_en,  # type: ignore
            text=self.text if language == Language.CN else self.text_en,  # type: ignore
            status=ArticleStatus(self.status),
            cover=self.cover,  # type: ignore
            create_time=self.create_time,  # type: ignore
            update_time=self.update_time,  # type: ignore
        )

    @classmethod
    async def get_by_id(cls, id_: int) -> "News":
        if not id_:
            raise AppException(StatusCode.NewsByIdNotFound, detail="新闻ID不能为空")

        stmt = select(cls).where(cls.id == id_).limit(1)
        try:
            async with AsyncSession() as session:
                async with session.begin():
                    news = await session.execute(stmt)
                    news = news.scalar()
                    if not news:
                        raise AppException(StatusCode.NewsByIdNotFound, detail="新闻不存在")
        except Exception as e:
            raise AppException(StatusCode.QueryProductError, detail=str(e))

        return news

    @classmethod
    async def get(cls, id_: int = None, not_id: int = None, title: str = None,
                  news_type: int = None, time: tuple[datetime | None, datetime | None, TimeKey] = None,
                  status: ArticleStatus = ArticleStatus.Publisher,
                  sort: tuple[Literal["id", TimeKey.CreateTime, TimeKey.UpdateTime], Sort] = ("id", Sort.Desc),
                  page: int = 1, page_size: int = 12) -> tuple[list["News"], int]:
        """
        新闻查询
        """
        stmt = select(cls)
        if id_:
            stmt = stmt.where(cls.id == id_)
        if not_id:
            stmt = stmt.where(cls.id!= not_id)
        if title:
            stmt = stmt.where(or_(
                cls.title.like(f"%{title}%"),
                cls.title_en.like(f"%{title}%")
            ))
        if news_type:
            stmt = stmt.where(cls.news_type == news_type)

        if time:
            tk = cls.create_time
            if time[2] == TimeKey.UpdateTime:
                tk = cls.update_time
            if time[0]:
                stmt = stmt.where(tk >= time[0])
            if time[1]:
                stmt = stmt.where(tk <= time[1])

        if status:
            stmt = stmt.where(cls.status == status.value)
        if sort:
            key = cls.id
            if sort[0] == "id":
                key = cls.id
            elif sort[0] == TimeKey.CreateTime:
                key = cls.create_time
            elif sort[0] == TimeKey.UpdateTime:
                key = cls.update_time
            order_by = key.asc()
            if sort[1] == Sort.Desc:
                order_by = key.desc()
            elif sort[1] == Sort.Asc:
                order_by = key.asc()
            stmt = stmt.order_by(order_by)
        if page and page_size:
            stmt = stmt.offset((page - 1) * page_size).limit(page_size)

        try:
            async with AsyncSession() as session:
                async with session.begin():
                    count_stmt = select(func.count()).select_from(stmt.alias("subquery"))
                    count = await session.execute(count_stmt)
                    count = count.scalar()

                    news_items = await session.execute(stmt)
                    news_items = news_items.scalars().all()
                    if not news_items:
                        news_items = []
        except Exception as e:
            raise AppException(StatusCode.QueryNewsError, detail=str(e))

        return news_items, count
